using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

public class Databank {
	static Databank databankInstantie = new Databank();
	SqlConnection connection;
	SqlCommand command;

	private Databank() {
		string conString;

		//lokale db gebruiken:
		//conString = "Data Source=matthysbart\\sqlexpress;Initial Catalog=StudieTijdMeting;Integrated Security=True";

		//nt1 db gebruiken:
		conString = "Data Source=NT1;Initial Catalog=S5SDB2;User Id=S5SDB2;Password=S5SDB2";

		connection = new SqlConnection(conString);
		command = new SqlCommand();
		command.Connection = connection;
	}

	static public Databank DatabankInstantie {
		get {
			return databankInstantie;
		}
	}

	public SqlDataReader GetSQLReader(string sql) {
		command.CommandText = sql;
		return command.ExecuteReader();
	}

	public void SQLExecuteNonQuery(string sql) {
		command.CommandText = sql;
		command.ExecuteNonQuery();
	}

	public SqlConnection GetConnection() {
		return connection;
	}

	private Object GetNewId(string table, string column) {
		SqlDataReader dr = GetSQLReader("select " + column + " from " + table + " where " + column + " = SCOPE_IDENTITY()");
		dr.Read();
		Object newId = dr.GetValue(0);
		dr.Close();

		return newId;
	}

	public List<String> GetGebruikers() {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select gebruikernaam from gebruiker");
		List<String> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}
		dr.Close();

		CloseConnection();

		return list;
	}

	public List<String> GetRollen() {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select rol from code");
		List<String> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}
		dr.Close();

		CloseConnection();

		return list;
	}

	public List<String> GetRollenGebruiker(string gid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select rol from code join code_gebruiker on code_gebruiker.code_id = code.code_id where code_gebruiker.gebruikernaam = '" + gid + "'");
		List<string> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}
		dr.Close();

		CloseConnection();

		return list;
	}

	public List<AgendaItem> GetAgendaRichting(int rid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select agenda_item.* from agenda_item join agenda_agenda_item on agenda_item.agenda_item_id = agenda_agenda_item.agenda_item_id join richting on agenda_agenda_item.agenda_id = richting.standaard_agenda where richting.richting_id = " + rid);
		List<AgendaItem> list = new List<AgendaItem>();

		while (dr.Read()) {
			list.Add(new AgendaItem(dr.GetInt32(0), dr.GetInt32(4), dr.GetDateTime(1), dr.GetInt32(2), dr.GetBoolean(3)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public List<AgendaItem> GetAgendaGebruiker(string gid, DateTime beginDatum, DateTime eindDatum) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select agenda_item.* from agenda_item join agenda_agenda_item on agenda_item.agenda_item_id = agenda_agenda_item.agenda_item_id join gebruiker on gebruiker.agenda_id = agenda_agenda_item.agenda_id where gebruikernaam = '" + gid + "'");
		List<AgendaItem> list = new List<AgendaItem>();

		while (dr.Read()) {
			if ((dr.GetDateTime(1) >= beginDatum) && (dr.GetDateTime(1) <= eindDatum)) {
				list.Add(new AgendaItem(dr.GetInt32(0), dr.GetInt32(4), dr.GetDateTime(1), dr.GetInt32(2), dr.GetBoolean(3)));
			}
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public void AddAgendaItemGebruiker(AgendaItem ai, string gid) {
		if (GetAgendaGebruiker(gid, ai.Datum, ai.Datum + new TimeSpan(0, ai.AantalMin, 0)).Count == 0) {
			OpenConnection();

			int cu;
			if (ai.IsContactUur) {
				cu = 1;
			} else {
				cu = 0;
			}

			SQLExecuteNonQuery("insert into agenda_item (datum_starttijd, [aantal min], [contact uur], vak_id) values ('" + ai.Datum.ToString("MM/dd/yyyy HH:mm") + "'," + ai.AantalMin + "," + cu + "," + ai.VakId + ")");

			int aiid = (int)GetNewId("agenda_item", "agenda_item_id");

			SqlDataReader dr = GetSQLReader("select agenda_id from gebruiker where gebruikernaam = '" + gid + "'");
			dr.Read();
			int aid = dr.GetInt32(0);
			dr.Close();

			SQLExecuteNonQuery("insert into agenda_agenda_item (agenda_item_id, agenda_id) values (" + aiid + ", " + aid + ")");

			CloseConnection();
		}

	}

	public void AddAgendaItemRichting(AgendaItem ai, int rid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into agenda_item (datum_starttijd, [aantal min], [contact uur], vak_id) values ('" + ai.Datum.ToString("MM/dd/yyyy HH:mm") + "'," + ai.AantalMin + "," + 1 + "," + ai.VakId + ")");

		int aiid = (int)GetNewId("agenda_item", "agenda_item_id");
        CloseConnection();
		int aid = GetRichting(rid).AgendaId;
        OpenConnection();
		SQLExecuteNonQuery("insert into agenda_agenda_item (agenda_item_id, agenda_id) values (" + aiid + ", " + aid + ")");

		CloseConnection();
	}

	public void DelAgendaItem(int aiid) {
		OpenConnection();

		SQLExecuteNonQuery("delete from agenda_item where agenda_item_id = " + aiid);

		SQLExecuteNonQuery("delete from agenda_agenda_item where agenda_item_id = " + aiid);

		CloseConnection();
	}

	public int AddAgenda(string naam) {
		OpenConnection();

		SQLExecuteNonQuery("insert into agenda (agenda_naam) values ('" + naam + "')");
		int aid = (int)GetNewId("agenda", "agenda_id");

		CloseConnection();

		return aid;
	}

	public void AddGebruiker(int aid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into gebruiker values ('" + gid + "', " + aid + ")");

		CloseConnection();
	}

	public void AddRol(int code, string rol) {
		OpenConnection();

		SQLExecuteNonQuery("insert into code values ('" + rol + "', " + code + ")");

		CloseConnection();
	}

	public void AddRolGebruiker(string gid, string rol) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select code_id from code where rol = '" + rol + "'");
		dr.Read();
		int code = dr.GetInt32(0);
		dr.Close();

		SQLExecuteNonQuery("insert into code_gebruiker values ('" + gid + "'," + code + ")");

		CloseConnection();
	}

	public void DelRol(string rol) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select code_id from code where rol = '"+rol+"'");
		dr.Read();
		int code_id = dr.GetInt32(0);
		dr.Close();

		SQLExecuteNonQuery("delete from code_gebruiker where code_id = "+code_id);

		SQLExecuteNonQuery("delete from code where code_id = " + code_id);

		CloseConnection();
	}

	public void DelRolGebruiker(string gid, string rol) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select code_id from code where rol = '" + rol + "'");
		dr.Read();
		int code_id = dr.GetInt32(0);
		dr.Close();

		SQLExecuteNonQuery("delete from code_gebruiker where code_id = " + code_id + " and gebruikernaam = '"+gid+"'");

		CloseConnection();
	}

	public int AddRichting(Richting r) {
		OpenConnection();

		SQLExecuteNonQuery("insert into richting (standaard_agenda, naam, afstudeerrichting, opleidingscoordinator) values (" + r.AgendaId + ",'" + r.Naam + "','" + r.AfstudeerRichting + "','" + r.OpleidingsCoordinator + "')");

		int rid = (int)GetNewId("richting", "richting_id");

		CloseConnection();

		return rid;
	}

	public void AddGebruikerRichting(int rid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into gebruiker_richting (richting_id, Gebruikernaam) values (" + rid + ",'" + gid + "')");

		CloseConnection();
	}

	public void DelGebruikerRichting(int rid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("delete from gebruiker_richting where richting_id = '"+rid+"' and Gebruikernaam = '"+gid+"'");

		CloseConnection();
	}

	public void DelGebruiker(String gid) {
		OpenConnection();

		SqlDataReader dr;

		dr = GetSQLReader("select agenda_id from gebruiker where gebruikernaam = '"+gid+"'");
		dr.Read();
		int aid = dr.GetInt32(0);
		dr.Close();

		dr = GetSQLReader("select agenda_item_id from agenda_agenda_item where agenda_id = " + aid);
		List<int> aiids = new List<int>();
		while (dr.Read()) {
			aiids.Add(dr.GetInt32(0));
		}
		dr.Close();

		SQLExecuteNonQuery("delete from code_gebruiker where gebruikernaam = '"+gid+"'");

		SQLExecuteNonQuery("delete from vak_docent where gebruikernaam = '"+gid+"'");

		SQLExecuteNonQuery("delete from gebruiker_richting where gebruikernaam = '"+gid+"'");

		SQLExecuteNonQuery("delete from gebruiker_mentor where student = '"+gid+"' or mentor = '"+gid+"'");

		SQLExecuteNonQuery("delete from afwezigheidlijst where gebruikernaam = '"+gid+"'");

		SQLExecuteNonQuery("delete from gebruiker where gebruikernaam = '"+gid+"'");

		foreach (int aiid in aiids){
			SQLExecuteNonQuery("delete from agenda_agenda_item where agenda_item_id = "+aiid);

			SQLExecuteNonQuery("delete from agenda_item where agenda_item_id = "+aiid);
		}

		SQLExecuteNonQuery("delete from agenda where agenda_id = '" + aid + "'");
		
		CloseConnection();
	}

	public void UpdateRichting(Richting r) {
		OpenConnection();

		SQLExecuteNonQuery("update richting set naam = '" + r.Naam + "', afstudeerrichting = '" + r.AfstudeerRichting + "', opleidingscoordinator = '" + r.OpleidingsCoordinator + "' where richting_id = " + r.Id);

		CloseConnection();
	}

	public void UpdateVak(Vak v) {
		OpenConnection();

		SQLExecuteNonQuery("update vakken set vaknaam = '"+v.Naam+"', code = '"+v.Code+"' where vak_id = "+v.Id);

		CloseConnection();
	}

	public List<Richting> GetRichtingen() {
		OpenConnection();

		List<Richting> list = new List<Richting>();
		SqlDataReader dr = GetSQLReader("select * from richting");

		while (dr.Read()) {
			list.Add(new Richting(dr.GetInt32(0), dr.GetString(2), dr.GetString(3), dr.GetInt32(1), dr.GetString(4)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public Richting GetRichting(int rid) {
        OpenConnection();

        SqlDataReader dr = GetSQLReader("select * from richting where richting_id = " + rid);

		dr.Read();

		Richting r = new Richting(dr.GetInt32(0), dr.GetString(2), dr.GetString(3), dr.GetInt32(1), dr.GetString(4));

		dr.Close();

		CloseConnection();

		return r;
	}

	public List<String> GetRichtingGebruikers(int rid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select gebruikernaam from gebruiker_richting where richting_id = " + rid);
		List<String> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}
		dr.Close();

		CloseConnection();

		return list;
	}

	public List<Richting> GetGebruikerRichtingen(string gid) {
        OpenConnection();

		List<Richting> list = new List<Richting>();
		SqlDataReader dr = GetSQLReader("select richting.* from richting join gebruiker_richting on richting.richting_id = gebruiker_richting.richting_id where Gebruikernaam = '" + gid + "'");

		while (dr.Read()) {
			list.Add(new Richting(dr.GetInt32(0), dr.GetString(2), dr.GetString(3), dr.GetInt32(1), dr.GetString(4)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}
    
	public int AddVak(Vak v) {
		OpenConnection();

		SQLExecuteNonQuery("insert into vakken (vaknaam, code) values ('" + v.Naam + "','" + v.Code + "')");

		int vid = (int)GetNewId("vakken", "vak_id");

		CloseConnection();

		return vid;
	}

	public void AddVakRichting(int rid, int vid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into richting_vak values (" + vid + "," + rid + ")");

		CloseConnection();
	}

	public void DelVakRichting(int rid, int vid) {
		OpenConnection();

		SQLExecuteNonQuery("delete from richting_vak where richting_id = "+rid+" and vak_id = "+vid);

		CloseConnection();
	}

	public List<Vak> GetVakken() {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select * from vakken");
		List<Vak> list = new List<Vak>();

		while (dr.Read()) {
			list.Add(new Vak(dr.GetInt32(0), dr.GetString(1), dr.GetString(2)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public List<Vak> GetVakkenRichting(int rid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select * from vakken join richting_vak on vakken.vak_id = richting_vak.vak_id where richting_id = " + rid);
		List<Vak> list = new List<Vak>();

		while (dr.Read()) {
			list.Add(new Vak(dr.GetInt32(0), dr.GetString(1), dr.GetString(2)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

    public List<Vak> GetVakkenRichtingStudent(String gebruiker)
    {
        OpenConnection();
        String qry = "select  v.* from vakken v join richting_vak rv on rv.vak_id = v.vak_id ";
        qry += "join gebruiker_richting gr on gr.Richting_id = rv.richting_id where gr.Gebruikernaam = '" + gebruiker + "'";
        
        SqlDataReader dr = GetSQLReader(qry);
        List<Vak> list = new List<Vak>();
        
        while (dr.Read())
        {
            list.Add(new Vak(dr.GetInt32(0), dr.GetString(1), dr.GetString(2)));
        }

        dr.Close();

        CloseConnection();

        return list;
    }

	public Vak GetVak(int vid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select * from vakken where vak_id = " + vid);

		dr.Read();

		Vak v = new Vak(dr.GetInt32(0), dr.GetString(1), dr.GetString(2));

		dr.Close();

		CloseConnection();

		return v;
	}

	public void AddDocentVak(int vid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into vak_docent values (" + vid + "," + gid + ")");

		CloseConnection();
	}

	public void DelDocentVak(int vid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("delete from docent_vak where vak_id = "+vid+" and gebruikernaam = '"+gid+"'");

		CloseConnection();
	}

	public List<Vak> GetDocentVakken(string gid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select vakken.* from vakken join vak_docent on vakken.vak_id = vak_docent.vak_id where gebruikernaam = '"+gid+"'");
		List<Vak> list = new List<Vak>();

		while (dr.Read()) {
			list.Add(new Vak(dr.GetInt32(0), dr.GetString(1), dr.GetString(2)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public List<String> GetMentorGebruikers(string mid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select student from gebruiker_mentor where mentor = '" + mid + "'");
		List<string> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public void AddMentorGebruiker(string mid, string gid, int rid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into gebruiker_mentor (mentor, student, richting_id) values ('" + mid + "','" + gid + "'," + rid + ")");

		CloseConnection();
	}

	public void DelMentorGebruiker(string mid, string gid, int rid) {
		OpenConnection();

		SQLExecuteNonQuery("delete from gebruiker_mentor where mentor = '"+mid+"' and student = '"+gid+"' and richting_id="+rid);

		CloseConnection();
	}

	public void AddAfwezigheid(int aiid, string gid) {
		OpenConnection();

		SQLExecuteNonQuery("insert into afwezigheidlijst (gebruikernaam, agenda_item_id) values ('" + gid + "'," + aiid + ")");

		CloseConnection();
	}

	public List<String> GetAfwezigheden(int aiid) {
		OpenConnection();

		SqlDataReader dr = GetSQLReader("select gebruikernaam from afwezigheidlijst where agenda_item_id = " + aiid);
		List<string> list = new List<string>();

		while (dr.Read()) {
			list.Add(dr.GetString(0));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

	public Boolean GetAanwezigheid(string gid, AgendaItem ai) {
		OpenConnection();

		Boolean aanwezig = true;

		int cu = 0;
		if (ai.IsContactUur) {
			cu = 1;
		}

		SqlDataReader dr = GetSQLReader("select agenda_item.agenda_item_id from gebruiker join agenda on gebruiker.agenda_id = agenda.agenda_id join agenda_agenda_item on agenda.agenda_id = agenda_agenda_item.agenda_id join agenda_item on agenda_item.agenda_item_id = agenda_agenda_item.agenda_item_id where gebruikernaam = '" + gid + "' and vak_id = " + ai.VakId + " and [contact uur] = " + cu + " and Datum_starttijd = " + ai.Datum.ToString("MM/dd/yyyy HH:mm"));
		if (!dr.Read()) {
			aanwezig = false;
		}
		dr.Close();

		CloseConnection();

		return true;
	}

	public List<Richting> GetRichtingenOpleidingscoordinator(string gid) {
		OpenConnection();

		List<Richting> list = new List<Richting>();
		SqlDataReader dr = GetSQLReader("select * from richting where opleidingscoordinator = '"+gid+"'");

		while (dr.Read()) {
			list.Add(new Richting(dr.GetInt32(0), dr.GetString(2), dr.GetString(3), dr.GetInt32(1), dr.GetString(4)));
		}

		dr.Close();

		CloseConnection();

		return list;
	}

    public double GetGemiddeldStudieTijdPerVak(string vakid)
    {
        OpenConnection();

        double tijd=0;
        try
        {
            SqlDataReader dr = GetSQLReader("select avg([Aantal min]) from Agenda_item join agenda_agenda_item on agenda_agenda_item.agenda_item_id = agenda_item.agenda_item_id where Vak_id = '" + vakid + "'");
            while (dr.Read())
            {
                tijd = double.Parse(dr.GetValue(0).ToString());
            }
            dr.Close();
        }
        catch (Exception ex)
        {

        }

        CloseConnection();
        return tijd;
    }

    public double GetStudieTijdPerVak(string vakid,String AgendaID)
    {

        OpenConnection();
        double tijd=0;

        string qry = "select sum([Aantal min])/60";
        qry += " from agenda_item ai join Agenda_Agenda_item aai on aai.Agenda_item_id = ai.Agenda_item_id ";
        qry += "where aai.agenda_id =  '" + AgendaID + "' and ai.Vak_id =  '" + vakid + "' ";
        try
        {
        SqlDataReader dr = GetSQLReader(qry);
        while(dr.Read())
        {
            tijd = double.Parse(dr.GetValue(0).ToString());
        }
            dr.Close();
        }
        catch (Exception ex)
        {

        }

        CloseConnection();
        return tijd;
    }

    public double GetStudieTijdPerVak(string vakid)
    {
        OpenConnection();
        double tijd = 0;

        string qry = "select sum([Aantal min])/60  from agenda_item " ;
        qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
        qry += "where Vak_id= '" + vakid + "'  and Agenda_id not in ( select Standaard_agenda from richting )";

        try
        {
            SqlDataReader dr = GetSQLReader(qry);
            while (dr.Read())
            {
                tijd = double.Parse(dr.GetValue(0).ToString());
            }
            dr.Close();
        }
        catch (Exception ex)
        {

        }

        CloseConnection();
        return tijd;


    }

    public double GetStudieTijdPerStudentVoorVak(string vid, string gebruiker)
    {
        OpenConnection();
        double tijd = 0;

        string qry = "select sum([Aantal min])/60  from agenda_item ";
        qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
        qry += "join gebruiker on gebruiker.Agenda_id = agenda_agenda_item.Agenda_id ";
        qry +=  "where Vak_id = '" + vid + "' and Gebruiker.Gebruikernaam = '"+gebruiker+"'";

        try
        {
            SqlDataReader dr = GetSQLReader(qry);
            while (dr.Read())
            {
                tijd = double.Parse(dr.GetValue(0).ToString());
            }
            dr.Close();
        }
        catch (Exception ex)
        {
        }
        CloseConnection();
        return tijd;

    }

    //public double GetStudieTijdPerStudentVoorVakMetRichting(string vid, string gebruiker,int richting)
    //    {

    //    string qry = "    select  sum([Aantal min])/60 from agenda_item  ";
    //    qry += "join richting_vak on agenda_item.Vak_id = richting_vak.Vak_id ";
    //    qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
    //    qry +=  "join gebruiker on gebruiker.Agenda_id = agenda_agenda_item.Agenda_id ";
    //    qry +=  "where agenda_item.Vak_id = and richting_vak.Richting_id= 3 and gebruiker.Gebruikernaam = 'christophe.dauwe@howest.be'
    //    }

    public double GetStudieTijdPerStudentVoorVak(string vid)
    {
        OpenConnection();
        double tijd = 0;

        string qry = "select sum([Aantal min])/60  from agenda_item ";
        qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
        qry += "join gebruiker on gebruiker.Agenda_id = agenda_agenda_item.Agenda_id ";
        qry += "where Vak_id = '" + vid + "' ";

        try
        {
            SqlDataReader dr = GetSQLReader(qry);
            while (dr.Read())
            {
                tijd = double.Parse(dr.GetValue(0).ToString());
            }
            dr.Close();
        }
        catch (Exception ex)
        {
        }
        CloseConnection();
        return tijd;

    }

    public string GetAgendaID(string gebruiker)
    {
        OpenConnection();
        string id="";

        string qry = "select * from gebruiker where Gebruikernaam = '" + gebruiker + "'";
        SqlDataReader dr = GetSQLReader(qry);

        while (dr.Read())
        {
            id = dr.GetValue(1).ToString();
        }

        dr.Close();
        CloseConnection();
        return id;

    }

    public string GetAgendaIDRichting(string rid)
    {
        OpenConnection();
        string id="";

        string qry = "select Standaard_agenda from richting where richting_id = '" + rid + "'";
        SqlDataReader dr = GetSQLReader(qry);

        while (dr.Read())
        {
            id = dr.GetValue(0).ToString();
        }

        dr.Close();
        CloseConnection();
        return id;
    }

    public List<double> GetStudietijdVakGBStudent(string vid)
    {
        OpenConnection();
        string qry = "select sum([Aantal min])  from agenda_item ";
        qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
        qry += "join gebruiker on gebruiker.Agenda_id = agenda_agenda_item.Agenda_id ";
        qry += " where Vak_id = " + vid;
        qry += " group by gebruiker.Gebruikernaam";

        List<double> tijd = new List<double>();
        try
        {

            SqlDataReader dr = GetSQLReader(qry);

            while (dr.Read())
            {
                tijd.Add(int.Parse(dr.GetValue(0).ToString()));
            }
            dr.Close();
        }
        catch (Exception ex)
        {

        }
        CloseConnection();

        return tijd;
    }

    public string GetMentorRichting(string mid)
    {
        OpenConnection();

        string qry = "select top 1 richting_id from gebruiker_mentor where Mentor = '" + mid + "'";

        string richtingid="0";

        SqlDataReader dr = GetSQLReader(qry);

        while (dr.Read())
        {
            richtingid = dr.GetValue(0).ToString();
        }
        dr.Close();
        CloseConnection();
        return richtingid;
    }

    public List<double> GetStudietijdVakGBStudent(string vid,DateTime start,DateTime end)
    {
        //ai.Datum.ToString("MM/dd/yyyy hh:mm")
        OpenConnection();

       string startc;
       string endc;

        startc = start.ToString("yyyy/MM/dd hh:mm");
        endc = end.ToString("yyyy/MM/dd hh:mm");

        start = DateTime.Parse(startc);
        end = DateTime.Parse(endc);

        string qry = "select sum([Aantal min])  from agenda_item ";
        qry += "join agenda_agenda_item on agenda_agenda_item.Agenda_item_id = agenda_item.Agenda_item_id ";
        qry += "join gebruiker on gebruiker.Agenda_id = agenda_agenda_item.Agenda_id ";
        qry += " where Vak_id = " + vid;
        qry += " and Datum_starttijd >=  '" + startc + "' and Datum_starttijd <= '" + endc + "'";
        qry += " group by gebruiker.Gebruikernaam";

        List<double> tijd = new List<double>();
        try
        {

            SqlDataReader dr = GetSQLReader(qry);

            while (dr.Read())
            {
                tijd.Add(int.Parse(dr.GetValue(0).ToString()));
            }
            dr.Close();
        }
        catch (Exception ex)
        {

        }
        CloseConnection();

        return tijd;
    }

    public bool Ismentor(string gid)
    {
        bool m = false;

        OpenConnection();

        string qry = "select count(*) from gebruiker_mentor where Mentor = '" + gid + "'";

        SqlDataReader dr = GetSQLReader(qry);
        int aantal = 0;
        while (dr.Read())
        {
            aantal = int.Parse(dr.GetValue(0).ToString());
        }

        if (aantal > 0)
        {
            m = true;
        }
        dr.Close();


        CloseConnection();

        return m;
    }

    public bool IsMentorZonderStudenten(string gid)
    {
        bool m = false;

        OpenConnection();


        string qry = "select count(*) from code_gebruiker where code_id = 5 and gebruikernaam = '" + gid + "'";

        SqlDataReader dr = GetSQLReader(qry);
        int aantal = 0;
        while (dr.Read())
        {
            aantal = int.Parse(dr.GetValue(0).ToString());
        }

        if (aantal != 0)
        {
            m = true;
        }
        dr.Close();

        CloseConnection();
        return m;
    }

    public bool IsCoordinator(string gid)
    {
        bool m = false;

        OpenConnection();

        string qry = "select count(*) from richting where opleidingscoordinator = '" + gid + "'";

        SqlDataReader dr = GetSQLReader(qry);
        int aantal = 0;
        while (dr.Read())
        {
            aantal = int.Parse(dr.GetValue(0).ToString());
        }

        if (aantal > 0)
        {
            m = true;
        }
        dr.Close();

        CloseConnection();

        return m;
    }

	public void OpenConnection(){
		if (connection.State != ConnectionState.Open) {
			connection.Open();
		}
	}

	public void CloseConnection() {
		if (connection.State != ConnectionState.Closed) {
			connection.Close();
		}
	}

    public bool IsBeheerder(string gid)
    {
        bool B = false;

        OpenConnection();

        string qry = "select count(*) from code_gebruiker where code_id = 4 and gebruikernaam = '" + gid + "'";

        SqlDataReader dr = GetSQLReader(qry);
        int aantal = 0;
        while (dr.Read())
        {
            aantal = int.Parse(dr.GetValue(0).ToString());
        }

        if (aantal != 0)
        {
            B = true;
        }

        dr.Close();
        CloseConnection();
        return B;
    }

}
